﻿Imports System.Data.SqlServerCe

Namespace Data
    Public Class Environment
        Private Shared mPassword As String
        Private Shared mFilePath As String

        Private Shared mTraceStatements As Boolean
        Public Shared Property TraceStatements() As Boolean
            Get
                Return mTraceStatements
            End Get
            Set(ByVal value As Boolean)
                mTraceStatements = value
            End Set
        End Property

#Region " Events "
        Public Class TraceInfo
            Public Timestamp As DateTime
            Public Thread As String
            Public Transaction As String
            Public Connection As String
            Public Statement As String
        End Class
        Public Shared Event DBTraceInfo(ByVal e As TraceInfo)
        Friend Shared Sub OnDBTraceInfoCommand(ByVal command As Command)
            If mTraceStatements Then
                Dim clsE As New TraceInfo
                clsE.Timestamp = Now
                clsE.Thread = System.Threading.Thread.CurrentThread.ManagedThreadId & ":" & System.Threading.Thread.CurrentThread.Name
                If command.Transaction IsNot Nothing Then
                    clsE.Transaction = command.Transaction.Id.ToString
                Else
                    clsE.Transaction = String.Empty
                End If
                clsE.Connection = command.Connection.Id.ToString
                clsE.Statement = command.CommandText
                OnDBTraceInfo(clsE)
            End If
        End Sub
        Friend Shared Sub OnDBTraceInfoOpenConnection(ByVal connection As Connection)
            If mTraceStatements Then
                Dim clsE As New TraceInfo
                clsE.Timestamp = Now
                clsE.Thread = System.Threading.Thread.CurrentThread.ManagedThreadId & ":" & System.Threading.Thread.CurrentThread.Name
                clsE.Transaction = String.Empty
                clsE.Connection = connection.Id.ToString
                clsE.Statement = String.Format("OPEN CONNECTION {0}", connection.Id)
                OnDBTraceInfo(clsE)
            End If
        End Sub
        Friend Shared Sub OnDBTraceInfoCloseConnection(ByVal connection As Connection)
            If mTraceStatements Then
                Dim clsE As New TraceInfo
                clsE.Timestamp = Now
                clsE.Thread = System.Threading.Thread.CurrentThread.ManagedThreadId & ":" & System.Threading.Thread.CurrentThread.Name
                clsE.Transaction = String.Empty
                clsE.Connection = connection.Id.ToString
                clsE.Statement = String.Format("CLOSE CONNECTION {0}", connection.Id)
                OnDBTraceInfo(clsE)
            End If
        End Sub
        Friend Shared Sub OnDBTraceInfoTransactionCommit(ByVal transactionScope As TransactionScope)
            If mTraceStatements Then
                Dim clsE As New TraceInfo
                clsE.Timestamp = Now
                clsE.Thread = System.Threading.Thread.CurrentThread.ManagedThreadId & ":" & System.Threading.Thread.CurrentThread.Name
                clsE.Transaction = transactionScope.Id.ToString
                clsE.Connection = transactionScope.Connection.Id.ToString
                clsE.Statement = String.Format("COMMIT TRANSACTION {0}", transactionScope.Id)
                OnDBTraceInfo(clsE)
            End If
        End Sub
        Friend Shared Sub OnDBTraceInfoTransactionStart(ByVal transactionScope As TransactionScope)
            If mTraceStatements Then
                Dim clsE As New TraceInfo
                clsE.Timestamp = Now
                clsE.Thread = System.Threading.Thread.CurrentThread.ManagedThreadId & ":" & System.Threading.Thread.CurrentThread.Name
                clsE.Transaction = transactionScope.Id.ToString
                clsE.Connection = transactionScope.Connection.Id.ToString
                clsE.Statement = String.Format("BEGIN TRANSACTION {0}", transactionScope.Id)
                OnDBTraceInfo(clsE)
            End If
        End Sub
        Friend Shared Sub OnDBTraceInfoTransactionRollback(ByVal transactionScope As TransactionScope)
            If mTraceStatements Then
                Dim clsE As New TraceInfo
                clsE.Timestamp = Now
                clsE.Thread = System.Threading.Thread.CurrentThread.ManagedThreadId & ":" & System.Threading.Thread.CurrentThread.Name
                clsE.Transaction = transactionScope.Id.ToString
                clsE.Connection = transactionScope.Connection.Id.ToString
                clsE.Statement = String.Format("ROLLBACK TRANSACTION {0}", transactionScope.Id)
                OnDBTraceInfo(clsE)
            End If
        End Sub
        Private Shared Sub OnDBTraceInfo(ByVal e As TraceInfo)
            Framework.Threading.Invoke(AddressOf OnDBTraceInfo_Callback, e)
        End Sub
        Private Shared Sub OnDBTraceInfo_Callback(ByVal e As TraceInfo)
            RaiseEvent DBTraceInfo(e)
        End Sub


#End Region


        Public Shared Sub SetActiveDatabase(ByVal filePath As String, ByVal password As String)
            mFilePath = filePath
            mPassword = password
        End Sub

        Public Enum TestConnectionResult
            OK
            FileDoesNotExist
            InvalidDbFormat
            UnknownError
        End Enum

        Public Shared Function TestConnection(ByVal filePath As String, ByVal password As String) As TestConnectionResult
            If Not System.IO.File.Exists(filePath) Then Return TestConnectionResult.FileDoesNotExist
            Try
                Using clsConn As Connection = GetConnection(filePath, password)
                    Dim strTestSql As String = "select count(*) from information_schema.tables where table_name = 'MetaData'"
                    Dim clsCmd As Command = Environment.NewCommand(strTestSql, clsConn)
                    Dim aRet As Generic.List(Of Integer) = ExecuteCommandToIntegerList(clsCmd)
                    If aRet.Count <> 1 Then Return TestConnectionResult.InvalidDbFormat
                    If aRet(0) <> 1 Then Return TestConnectionResult.InvalidDbFormat
                    Return TestConnectionResult.OK
                End Using
            Catch ex As SqlCeInvalidDatabaseFormatException
                Return TestConnectionResult.InvalidDbFormat
            End Try
        End Function


        'Friend Shared Function GetConnectionString(ByVal filePath As String, ByVal password As String, ByVal newFormat As Boolean) As String
        Friend Shared Function GetConnectionString(ByVal filePath As String, ByVal password As String) As String
            Return String.Format("Data Source={0};Password={1};Encrypt Database=True", filePath, password)
            'Tax Removed newformat variable
            'If newFormat Then
            '    Return String.Format("Data Source={0};Password={1};Encrypt Database=True", filePath, password)
            'Else
            '    Return String.Format("Data Source={0};Password={1};", filePath, password)
            'End If
        End Function

        Friend Shared Function GetConnection() As Connection
            Return GetConnection(Source.Local)
        End Function
        Friend Shared Function GetConnection(ByVal source As Source) As Connection
            Select Case source
                Case source.Local
                    Return New Connection(New System.Data.SqlServerCe.SqlCeConnection(GetConnectionString(mFilePath, mPassword)))
                Case source.Template
                    Return New Connection(New System.Data.SqlServerCe.SqlCeConnection(GetConnectionString(TemplateDBPath, TemplateDBPassword)))
                Case Else
                    Throw New NotImplementedException
            End Select
        End Function
        Friend Shared Function GetConnection(ByVal filePath As String, ByVal password As String) As Connection
            Return New Connection(New System.Data.SqlServerCe.SqlCeConnection(GetConnectionString(filePath, password)))
        End Function



        Public Shared Function NewCommand(ByVal sql As String) As Command
            Return New Command(sql, GetConnection)
        End Function
        Friend Shared Function NewCommand(ByVal sql As String, ByVal source As Source) As Command
            Return New Command(sql, GetConnection(source))
        End Function
        Friend Shared Function NewCommand(ByVal sql As String, ByVal connection As Connection) As Command
            Return New Command(sql, connection)
        End Function
        Public Shared Function NewCommand(ByVal sql As String, ByVal transactionScope As TransactionScope) As Command
            Return New Command(sql, transactionScope)
        End Function
        Friend Shared Function NewCommand(ByVal sql As String, ByVal filePath As String, ByVal password As String) As Command
            Return New Command(sql, GetConnection(filePath, password))
        End Function

        ''' <summary>
        ''' It WILL overwrite any existing database!!!!!!! No questions asked!
        ''' </summary>
        ''' <param name="path"></param>
        ''' <param name="password"></param>
        ''' <remarks></remarks>
        Public Shared Sub CreateLocalDatabase(ByVal path As String, ByVal password As String)
            If System.IO.File.Exists(path) Then System.IO.File.Delete(path)

            'Tax
            'Dim strConn As String = GetConnectionString(path, password, True)
            Dim strConn As String = GetConnectionString(path, password)

            Using clsEngine As New SqlCeEngine(strConn)
                clsEngine.CreateDatabase()
            End Using

            mFilePath = path
            mPassword = password

            Setup.UpgradeLocalDatabase(True)
        End Sub

        Public Shared Function GetColumnInfos_Template(ByVal tableName As String) As Generic.List(Of ColumnInfo)
            Using t As New TransactionScope(Source.Template, TransactionScope.TransactionMode.NullTransaction)
                Return GetColumnInfos(tableName, t)
                t.Complete()
            End Using
        End Function

        Public Shared Function GetColumnInfos(ByVal tableName As String, ByVal transactionScope As TransactionScope) As Generic.List(Of ColumnInfo)
            Dim strSql As String = "select column_name,ordinal_position,is_nullable,data_type,character_maximum_length,autoinc_increment,autoinc_seed,column_default " & _
                                   "from information_schema.columns " & _
                                   "where table_name = @TableName " & _
                                   "order by ordinal_position"

            Dim sqlCmd As Command = NewCommand(strSql, transactionScope)
            sqlCmd.AddParameterWithValue("@TableName", tableName)
            Using dr As SqlCeDataReader = sqlCmd.ExecuteReader
                Dim aRet As New Generic.List(Of ColumnInfo)
                While dr.Read
                    Dim clsCol As New ColumnInfo
                    clsCol.Name = dr.GetString(0)
                    clsCol.OrdinalNr = dr.GetInt32(1)
                    clsCol.IsNullable = (dr.GetString(2) <> "NO")
                    clsCol.DataType = dr.GetString(3)
                    If Not dr.IsDBNull(4) Then clsCol.CharMaxLen = dr.GetInt32(4)
                    If Not dr.IsDBNull(5) Then clsCol.AutoInc_Increment = dr.GetInt64(5)
                    If Not dr.IsDBNull(6) Then clsCol.AutoInc_Seed = dr.GetInt64(6)
                    If Not dr.IsDBNull(7) Then
                        Dim oVal As Object = dr.GetValue(7)
                        If TypeOf oVal Is Integer Then
                            clsCol.DefaultValue = CInt(oVal).ToString
                        ElseIf TypeOf oVal Is String Then
                            clsCol.DefaultValue = CStr(oVal)
                        Else
                            clsCol.DefaultValue = CStr(oVal)
                        End If
                    End If
                    aRet.Add(clsCol)
                End While
                Return aRet
            End Using
        End Function
        Public Shared Function GetIndexInfos_Template(ByVal tableName As String) As IndexInfo()
            Dim strSql As String = "select index_name,primary_key,[unique],ordinal_position,column_name " & _
                                    "from information_schema.indexes " & _
                                    "where table_name = @TableName " & _
                                    "order by index_name,ordinal_position"


            Dim sqlCmd As Command = NewCommand(strSql, Source.Template)
            sqlCmd.AddParameterWithValue("@TableName", tableName)
            sqlCmd.Connection.Open()
            Try
                Environment.OnDBTraceInfoCommand(sqlCmd)
                Using dr As SqlCeDataReader = sqlCmd.ExecuteReader
                    Dim dIdx As New Generic.Dictionary(Of String, IndexInfo)
                    While dr.Read
                        Dim strIdxName As String = dr.GetString(0)
                        Dim clsIdx As IndexInfo
                        If dIdx.ContainsKey(strIdxName) Then
                            clsIdx = dIdx(strIdxName)
                        Else
                            clsIdx = New IndexInfo
                            clsIdx.Name = strIdxName
                            dIdx.Add(strIdxName, clsIdx)
                        End If
                        clsIdx.IsPrimaryKey = dr.GetBoolean(1) ' (dr.GetInt32(1) = 1)
                        clsIdx.IsUnique = dr.GetBoolean(2) ' (dr.GetInt32(2) = 1)
                        clsIdx.ColNames.Add(dr.GetString(4))
                    End While
                    Return dIdx.Values.ToArray
                End Using
            Finally
                sqlCmd.Connection.Close()
            End Try
        End Function
        Public Class IndexInfo
            Public Name As String
            Public IsPrimaryKey As Boolean
            Public IsUnique As Boolean
            Public ColNames As New Generic.List(Of String)
        End Class
        Public Class ColumnInfo
            Public Name As String
            Public OrdinalNr As Integer
            Public IsNullable As Boolean
            Public DataType As String
            Public CharMaxLen As Integer
            Public AutoInc_Increment As Long
            Public AutoInc_Seed As Long
            Public DefaultValue As String
        End Class

        Public Shared ReadOnly Property DBPath() As String
            Get
                Return mFilePath
            End Get
        End Property
        Public Shared ReadOnly Property DBPassword() As String
            Get
                Return mPassword
            End Get
        End Property

        Friend Shared ReadOnly Property TemplateDBPath() As String
            Get
                Dim strAppPath As String = AppDomain.CurrentDomain.BaseDirectory
                Return System.IO.Path.Combine(strAppPath, "DatabaseTemplates\Client.sdf")
            End Get
        End Property
        Friend Shared ReadOnly Property TemplateDBPassword() As String
            Get
                Return "b2b"
            End Get
        End Property

        Public Shared Function ExecuteNonQuery(ByVal command As Command) As Integer
            If command.Transaction Is Nothing Then command.Connection.Open()
            Try
                OnDBTraceInfoCommand(command)
                Return command.ExecuteNonQuery()
            Finally
                If command.Transaction Is Nothing Then command.Connection.Close()
            End Try

        End Function
        Public Shared Function ExecuteNonQuery(ByVal statement As String) As Integer
            Return ExecuteNonQuery(statement, Source.Local)
        End Function

        Public Shared Function ExecuteNonQuery(ByVal statement As String, ByVal source As Source) As Integer
            Dim sqlCmd As Command = NewCommand(statement, source)
            Return ExecuteNonQuery(sqlCmd)
        End Function

        Public Shared Function ExecuteNonQuery(ByVal statement As String, ByVal connection As Connection) As Integer
            Dim sqlCmd As Command = NewCommand(statement, connection)
            Return sqlCmd.ExecuteNonQuery()
        End Function

        Public Shared Function ExecuteNonQuery(ByVal statement As String, ByVal transactionScope As TransactionScope, ByVal ParamArray parameters() As SqlCeParameter) As Integer
            Dim sqlCmd As Command = NewCommand(statement, transactionScope)
            If parameters IsNot Nothing Then sqlCmd.AddParameterRange(parameters)
            Return ExecuteNonQuery(sqlCmd)
        End Function

        Public Shared Function SqlToStringList(ByVal statement As String, Optional ByVal source As Source = Source.Local, Optional ByVal columnIndex As Integer = 0) As Generic.List(Of String)
            Dim sqlCmd As Command = Environment.NewCommand(statement, source)
            Return ExecuteCommandToStringList(sqlCmd, columnIndex)
        End Function
        Public Shared Function SqlToStringList(ByVal statement As String, ByVal ParamArray parameters() As SqlCeParameter) As Generic.List(Of String)
            Dim sqlCmd As Command = Environment.NewCommand(statement)
            sqlCmd.AddParameterRange(parameters)
            Return ExecuteCommandToStringList(sqlCmd)
        End Function

        Public Shared Function ExecuteCommandToStringList(ByVal command As Command, Optional ByVal columnIndex As Integer = 0) As Generic.List(Of String)
            Try
                If command.Transaction Is Nothing Then command.Connection.Open()
                Try
                    OnDBTraceInfoCommand(command)
                    Using dr As SqlCeDataReader = command.ExecuteReader()
                        Dim aRet As New Generic.List(Of String)
                        While dr.Read
                            If dr.GetValue(columnIndex) Is DBNull.Value Then
                                aRet.Add(Nothing)
                            Else
                                aRet.Add(dr.GetString(columnIndex))
                            End If
                        End While
                        Return aRet
                    End Using
                Finally
                    If command.Transaction Is Nothing Then command.Connection.Close()
                End Try
            Catch ex As Exception
                Throw New Exception(String.Format("Failed executing query: {0}", command.CommandText), ex)
            End Try
        End Function
        Public Shared Function SqlToIntegerList(ByVal sql As String, ByVal ParamArray parameters() As SqlCeParameter) As Generic.List(Of Integer)
            Return SqlToIntegerListWorker(sql, 0, parameters)
        End Function
        Public Shared Function SqlToIntegerList(ByVal sql As String, ByVal columnIndex As Integer, ByVal ParamArray parameters() As SqlCeParameter) As Generic.List(Of Integer)
            Return SqlToIntegerListWorker(sql, columnIndex, parameters)
        End Function
        Private Shared Function SqlToIntegerListWorker(ByVal sql As String, ByVal columnIndex As Integer, ByVal parameters() As SqlCeParameter) As Generic.List(Of Integer)
            Dim sqlCmd As Command = Environment.NewCommand(sql)
            sqlCmd.AddParameterRange(parameters)
            Return ExecuteCommandToIntegerList(sqlCmd, columnIndex)
        End Function
        Public Shared Function ExecuteCommandToIntegerList(ByVal command As Command, Optional ByVal columnIndex As Integer = 0) As Generic.List(Of Integer)
            Try
                If command.Transaction Is Nothing Then command.Connection.Open()
                Try
                    OnDBTraceInfoCommand(command)
                    Using dr As SqlCeDataReader = command.ExecuteReader()
                        Dim aRet As New Generic.List(Of Integer)
                        While dr.Read
                            aRet.Add(dr.GetInt32(columnIndex))
                        End While
                        Return aRet
                    End Using
                Finally
                    If command.Transaction Is Nothing Then command.Connection.Close()
                End Try
            Catch ex As Exception
                Throw New Exception(String.Format("Failed executing query: {0}", command.CommandText), ex)
            End Try
        End Function

        Public Shared Function SqlToInfoObjects(Of T)(ByVal sql As String, ByVal ParamArray parameters() As SqlCeParameter) As Generic.List(Of T)
            Dim sqlCmd As Command = Environment.NewCommand(sql)
            If parameters IsNot Nothing Then sqlCmd.AddParameterRange(parameters)
            Return SqlToInfoObjects(Of T)(sqlCmd)
        End Function
        Public Shared Function SqlToInfoObjects(Of T)(ByVal command As Command) As Generic.List(Of T)
            If command.Transaction Is Nothing Then command.Connection.Open()
            Try
                Environment.OnDBTraceInfoCommand(command)
                Using dr As SqlCeDataReader = command.ExecuteReader()
                    Dim aRet As New Generic.List(Of T)
                    While dr.Read
                        Dim clsItem As T = CType(System.Activator.CreateInstance(GetType(T)), T)
                        For i As Integer = 0 To dr.FieldCount - 1
                            Dim strFieldName As String = dr.GetName(i)
                            Dim clsFI As Reflection.FieldInfo = GetType(T).GetField(strFieldName) ', Reflection.BindingFlags.Public Or Reflection.BindingFlags.GetField Or Reflection.BindingFlags.GetProperty Or Reflection.BindingFlags.IgnoreCase)
                            If clsFI IsNot Nothing Then
                                clsFI.SetValue(clsItem, dr.GetValue(i))
                            Else
                                Dim clsPI As Reflection.PropertyInfo = GetType(T).GetProperty(strFieldName)
                                clsPI.SetValue(clsItem, dr.GetValue(i), Nothing)
                            End If
                        Next
                        aRet.Add(clsItem)
                    End While
                    Return aRet
                End Using
            Catch ex As Exception
                Throw New Exception(String.Format("Failed executing query: {0}", command.CommandText), ex)
            Finally
                If command.Transaction Is Nothing Then command.Connection.Close()
            End Try
        End Function

    End Class

    Public Enum Source
        Local
        Template
    End Enum
End Namespace